package com.dev.trade.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import com.dev.trade.bo.Stock;
import com.dev.trade.bo.User;
import com.dev.trade.exception.DBException;
public class TradeDAO {
private Connection con;
public TradeDAO() throws Exception {
try {
InitialContext initialContext = new InitialContext();
Context envCtx = (Context) initialContext.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/TradeDB");
con = ds.getConnection();
} catch (Exception ex) {
throw new Exception("Couldn't open connection to database: "
+ ex.getMessage());
}
}
public User getUserByUserId(String userId) throws DBException {
User user = null;
try {
String query = "select * from users where userid = ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setString(1, userId);
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
user = new User(rs.getString(1), rs.getString(2), rs
.getString(3), rs.getString(4), rs.getFloat(5));
}
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
return user;
}
public List getUserStocks(String userId) throws DBException {
List stocks = new ArrayList();
try {
String query = "select A.quantity,A.stockid,B.name,B.price from TradingAccount A,stocks B where A.userid = ? and A.stockid = B.id";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setString(1, userId);
Stock stock = null;
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
stock = new Stock(rs.getString(2), rs.getString(3), rs
.getFloat(4), rs.getInt(1));
stocks.add(stock);
}
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
return stocks;
}
public List getStocks() throws DBException {
List stocks = new ArrayList();
try {
String query = "select * from stocks";
PreparedStatement pStmt = con.prepareStatement(query);
Stock stock = null;
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
stock = new Stock(rs.getString(1), rs.getString(2), rs
.getFloat(3), 0);
stocks.add(stock);
}
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
return stocks;
}
public float getUserCash(String userId) throws DBException {
float cash = 0;
try {
String query = "select cash from users where userid = ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setString(1, userId);
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
cash = rs.getFloat("cash");
}
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
return cash;
}
public boolean buyStock(String userId, String stockId, int quantity)
throws DBException {
float cashAvailable = getUserCash(userId);
float costOfStock = getStockPrice(stockId);
float totalCost = costOfStock * quantity;
int availableStock = getStockQuantityForUser(userId, stockId);
cashAvailable = cashAvailable - totalCost;
availableStock = availableStock + quantity;
if (cashAvailable >= 0 && (availableStock != quantity)) {
try {
con.setAutoCommit(false);
if (setUserCash(userId, cashAvailable)
&& setUserStock(userId, stockId, availableStock)) {
con.commit();
} else {
con.rollback();
}
con.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new DBException(e.getMessage());
}
} else if (cashAvailable >= 0 && (availableStock == quantity)) {
try {
con.setAutoCommit(false);
if (setUserCash(userId, cashAvailable)
&& addUserStock(userId, stockId, availableStock)) {
con.commit();
} else {
con.rollback();
}
con.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new DBException(e.getMessage());
}
} else {
return false;
}
return true;
}
public boolean sellStock(String userId, String stockId, int quantity)
throws DBException {
float cashAvailable = getUserCash(userId);
float costOfStock = getStockPrice(stockId);
float totalCost = costOfStock * quantity;
int availableStock = getStockQuantityForUser(userId, stockId);
cashAvailable = cashAvailable + totalCost;
availableStock = availableStock - quantity;
if (availableStock >= 0) {
try {
con.setAutoCommit(false);
if (setUserCash(userId, cashAvailable)
&& setUserStock(userId, stockId, availableStock)) {
con.commit();
} else {
con.rollback();
}
con.setAutoCommit(true);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
throw new DBException(e.getMessage());
}
} else {
return false;
}
return true;
}
public float getStockPrice(String stockId) throws DBException {
float stockPrice = 0f;
try {
String query = "select price from stocks where id = ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setString(1, stockId);
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
stockPrice = rs.getFloat("price");
}
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
return stockPrice;
}
private boolean setUserCash(String userId, float cash) throws DBException {
int status = 0;
try {
String query = "update users set cash = ? where userid = ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setFloat(1, cash);
pStmt.setString(2, userId);
status = pStmt.executeUpdate();
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
if (status == 0) {
return false;
} else {
return true;
}
}
// buy = true sell = false
private boolean setUserStock(String userId, String stockId, int quantity)
throws DBException {
int existingQuantity = 0;
int status = 0;
try {
String query = "update TradingAccount set quantity = ? where stockid = ? and userid = ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setInt(1, quantity);
pStmt.setString(2, stockId);
pStmt.setString(3, userId);
status = pStmt.executeUpdate();
pStmt.close();
query = "delete from TradingAccount where quantity = ?";
pStmt = con.prepareStatement(query);
pStmt.setInt(1, 0);
pStmt.executeUpdate();
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
if (status == 0) {
return false;
} else {
return true;
}
}
private boolean addUserStock(String userId, String stockId, int quantity)
throws DBException {
int status = 0;
try {
String query = "insert into TradingAccount values (?,?,?)";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setInt(3, quantity);
pStmt.setString(2, stockId);
pStmt.setString(1, userId);
status = pStmt.executeUpdate();
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
if (status == 0) {
return false;
} else {
return true;
}
}
public int getStockQuantityForUser(String userId, String stockId)
throws DBException {
int existingQuantity = 0;
try {
String query = "select quantity from TradingAccount where userid = ? and stockid = ?";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setString(1, userId);
pStmt.setString(2, stockId);
ResultSet rs = pStmt.executeQuery();
while (rs.next()) {
existingQuantity = rs.getInt("quantity");
}
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
return existingQuantity;
}
public boolean addUser(String userId, String name, String password,
String address, float cash) throws DBException {
int status = 0;
try {
String query = "insert into users values (?,?,?,?,?)";
PreparedStatement pStmt = con.prepareStatement(query);
pStmt.setString(1, userId);
pStmt.setString(2, name);
pStmt.setString(3, password);
pStmt.setString(4, address);
pStmt.setFloat(5, cash);
status = pStmt.executeUpdate();
pStmt.close();
} catch (SQLException ex) {
ex.printStackTrace();
throw new DBException(ex.getMessage());
}
if (status == 0) {
return false;
} else {
return true;
}
}
public void remove() throws DBException {
try {
con.close();
} catch (SQLException ex) {
throw new DBException(ex.getMessage());
}
}
}